﻿USE [RestaurantManagement]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Nguyễn Đức Thắng>
-- Create date: <15/03/2013>
-- Get_All_Restaurants
-- =============================================
CREATE PROCEDURE [dbo].[Get_Paged_Stocks_ByBranch]
		@WhereClause nvarchar (4000)  ,
		@OrderBy varchar (2000)  ,
		@PageIndex INT,
		@RestaurantID UNIQUEIDENTIFIER
AS
BEGIN
	DECLARE @PageSize INT
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound INT
	
	SELECT @PageSize = d.OptionValue FROM Admin.DBOption d
	WHERE d.OptionID = 'NumberOfPage'
	
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize
	
	Create Table #PageIndex
	(
		[IndexId] int IDENTITY (1, 1) NOT NULL,
		StockID UNIQUEIDENTIFIER
	)
	declare @SQL as nvarchar(4000)
	
	SET @SQL = 'INSERT INTO #PageIndex (StockID)'
	SET @SQL = @SQL + ' SELECT'
	IF @PageSize > 0
	BEGIN
	SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
	End 
	SET @SQL = @SQL + ' StockID'
	SET @SQL = @SQL + ' FROM Production.[Stock]'
	IF LEN(@WhereClause) > 0
	BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
	End
	IF LEN(@OrderBy) > 0
	BEGIN
		SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
	End
		exec sp_executesql @SQL
		select 	O.StockID,
				O.StockCode,
				O.StockName,
				O.Description,
				O.RestaurantID,
				O.IsActive
		FROM  Production.[Stock] AS O, [#PageIndex] AS PageIndex
		WHERE     (PageIndex.IndexID > @PageLowerBound)  AND (O.StockID = PageIndex.StockID)
				AND o.RestaurantID = @RestaurantID
		
	ORDER BY PageIndex.IndexID
	SET @SQL = 'SELECT COUNT(*) as TotalRecords'
	SET @SQL = @SQL + ' FROM Production.[Stock]'
	
	IF LEN(@WhereClause) > 0
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereClause
	END
	
	exec sp_executesql @SQL
END 
